#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pymysql
from pymysql.cursors import DictCursor

class MySQLConnector:
    """MySQL数据库连接类"""
    
    def __init__(self, host='localhost', user='root', password='', database='', port=3306, charset='utf8mb4'):
        """初始化数据库连接参数"""
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connection = None
        self.cursor = None
    
    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                charset=self.charset,
                cursorclass=DictCursor
            )
            self.cursor = self.connection.cursor()
            print(f"成功连接到数据库: {self.database}")
            return True
        except pymysql.MySQLError as e:
            print(f"数据库连接失败: {e}")
            return False
    
    def disconnect(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection and self.connection.open:
            self.connection.close()
            print("数据库连接已关闭")
    
    def execute_query(self, query, params=None):
        """执行SQL查询并返回结果"""
        if not self.connection or not self.connection.open:
            if not self.connect():
                return None
        
        try:
            self.cursor.execute(query, params)
            result = self.cursor.fetchall()
            return result
        except pymysql.MySQLError as e:
            print(f"查询执行失败: {e}")
            return None
    
    def execute_update(self, query, params=None):
        """执行SQL更新操作(插入、更新、删除)"""
        if not self.connection or not self.connection.open:
            if not self.connect():
                return False
        
        try:
            self.cursor.execute(query, params)
            self.connection.commit()
            print(f"操作成功，影响行数: {self.cursor.rowcount}")
            return True
        except pymysql.MySQLError as e:
            print(f"操作执行失败: {e}")
            self.connection.rollback()
            return False

# 使用示例
if __name__ == '__main__':
    # 创建数据库连接器实例
    # 注意：实际使用时请根据您的MySQL配置修改以下参数
    db_connector = MySQLConnector(
        host='localhost',    # MySQL服务器地址
        user='root',         # MySQL用户名
        password='123456', # MySQL密码
        database='test_video_path', # 数据库名
        port=3306            # MySQL端口
    )
    
    # 连接数据库
    if db_connector.connect():
        #执行查询示例
        result = db_connector.execute_query("SELECT * FROM video_path")
        if result:
            print("查询结果:")
            for row in result:
                print(row)
        
        # 关闭连接
        db_connector.disconnect()